﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using hes.models;
using hes.controllers;
using System.Data;
using System.Collections;

namespace hes.data_access
{
    //This class handles all data access between the application and the
    //termitem table in the database.
    class TermItemDAO
    {
        //private SqlConnection db_connection = prototype123.Program.connect.GetConnection();
        private SqlConnection db_connection; // = new SqlConnection("Data Source=sparky;Initial Catalog=cps420_team2_dev;Persist Security Info=True;User ID=cps420team2;Password=team2");

        public TermItemDAO(SqlConnection connection)
        {
            db_connection = connection;
        }

        /*precondition: the system needs TermItem information and has a stuid, termid, and courseid
           description: The system passes in the student's id (stuid), the current term for that
                  student (termid), and the courseid for the course they are taking (courseid). given that
                  info, this method queries the db for the termitem entry matching this info. it receives the
                  info from the query and creates a TermItem object to represent it, and then returns it
          postcondition: the system receives a TermItem object */
        public TermItem Load(int stuid, int termid, string courseid)
        {
            TermItem tm = new TermItem();
            SqlCommand getterm = new SqlCommand("SELECT * FROM TERM_ITEM WHERE STU_ID = " + stuid +
                " and TERM_ID = " + termid + "and COURSE_ID = '" + courseid + "'", db_connection);


            SqlDataReader read = getterm.ExecuteReader();
            read.Read();
            tm.CourseID = courseid;
            tm.Dropped = Convert.ToInt32(read["DROPPED"].ToString());
            tm.ID = Convert.ToInt32(read["ITEM_ID"].ToString());
            tm.TeacherType = read["TEACHER_TYPE"].ToString();
            tm.Textbook = read["TEXTBOOK"].ToString();
            tm.UsingHEICurriculum = Convert.ToInt32(read["USE_HEI_CURR"].ToString());
            //tm.FinalGrade = Convert.ToInt32(read["FINAL_GRADE"]);

            read.Close();
            return tm;
        }

        public ArrayList FindCurrentStudentTermItems(int stuid, int termid)
        {
            ArrayList term_items = new ArrayList();

            SqlCommand getterms = new SqlCommand("SELECT * FROM TERM_ITEM WHERE STU_ID = " + stuid +
                " and TERM_ID = " + termid, db_connection);
            SqlDataReader reader = getterms.ExecuteReader();

            while (reader.Read()) {
                TermItem tm = new TermItem();
                //reader.Read();
                tm.CourseID = reader["COURSE_ID"].ToString();
                tm.Dropped = Convert.ToInt32(reader["DROPPED"].ToString());
                tm.ID = Convert.ToInt32(reader["ITEM_ID"].ToString());
                tm.TeacherType = reader["TEACHER_TYPE"].ToString();
                tm.Textbook = reader["TEXTBOOK"].ToString();
                tm.UsingHEICurriculum = Convert.ToInt32(reader["USE_HEI_CURR"].ToString());
                term_items.Add(tm);
            }

            reader.Close();

            return term_items;
        }

        //redundant code dersch wrote for reasons unknown to mankind. save it just in case.
        //mostly just keep it to mock him.
        /*public void Save(TermItem itm, int stuid)
        {
            SqlCommand save = new SqlCommand("INSERT INTO TERM_ITEM(STU_ID, TERM_ID, COURSE_ID, FINAL_GRADE, DROPPED, " +
                "TEXTBOOK, USE_HEI_CURR, TEACHER_TYPE) VALUES(" + stuid + ", " + itm.ID + ", '" + itm.CourseID +
                "', " + itm.FinalGrade + ", " + itm.Dropped + ", '" + itm.Textbook + "', " + itm.UsingHEICurriculum +
                ", '" + Convert.ToChar(itm.TeacherType) + "')", db_connection);
            save.ExecuteNonQuery();
        }*/

        /*precondition: the educator adds a course AND chooses to use the HEI curriculum
        description: This method copies all default assignments from the PKG_ASMT db and copies them to
              the ASSIGNMENT database and matches it with the termID. It does the same for categories,
              copying them from PKG_ASMT_CAT to ASMT_CAT.
        postconditon: relevant assignments from the PKG_ASMT db and PKG_ASMT_CAT db are now in the
              ASSIGNMENT and ASMT_CAT databases*/
        public void AddCurriculum(string courseid, int termid)
        {
            SqlCommand AddAssignments = new SqlCommand("INSERT INTO ASSIGNMENT(CAT_ID, TITLE, PTS_POSSIBLE) SELECT" +
                " CAT_ID, TITLE, PTS_POSSIBLE FROM PKG_ASMT", db_connection);

            SqlCommand AddCategories = new SqlCommand("INSERT INTO ASMT_CAT(CAT_NAME, CAT_PERCENT, ITEM_ID) "
                + "SELECT CAT_NAME, CAT_PERCENT, " + termid + " FROM PKG_ASMT_CAT WHERE COURSE_ID = '" + 
                courseid + "'", db_connection);

            AddAssignments.ExecuteNonQuery();
            AddCategories.ExecuteNonQuery();
        }

        /// <precondition>Controller has info for a course registration</precondition>
        /// <summary>
        /// This registers a student into a course that he has not yet taken and passed.
        /// </summary>
        /// <param name="std">This is the student that is adding the course</param>
        /// <param name="termitem">This is the current term that the student is enrolled in</param>
        /// Postcondition: The student is enrolled in the course.
        public void AddCourse(Student std, TermItem termitem)
        {
            SqlCommand add_course = new SqlCommand("INSERT INTO TERM_ITEM(STU_ID, TERM_ID, COURSE_ID," + "DROPPED, " +
                "TEXTBOOK, USE_HEI_CURR, TEACHER_TYPE) Values(" + std.StudentID + "," + termitem.ID + ",'" + termitem.CourseID + "', " +
                 termitem.Dropped + ",'" + termitem.Textbook + "'," + termitem.UsingHEICurriculum + ", '" + Convert.ToChar(termitem.TeacherType) + "')", db_connection);

            add_course.ExecuteNonQuery();
            
            if (Convert.ToBoolean(termitem.UsingHEICurriculum)) { AddCurriculum(termitem.CourseID, termitem.ID); }
        }

        /*precondition: the educator needs to add a course that was dropped during this term
        description: This method executes a command that clears the DROPPED flag in the
              TERM_ITEM db. This allows the student to be back in the course.
        postcondition: the student is re-enrolled in the course */
        public void ReAddCourse(int stuid, string courseid, int termid)
        {
            SqlCommand re_add_course = new SqlCommand("UPDATE TERM_ITEM SET DROPPED = 0 WHERE " +
                "STU_ID = " + stuid + " and COURSE_ID = '" + courseid + "' and TERM_ID = " + termid, db_connection);
            re_add_course.ExecuteNonQuery();
        }

        /// <summary>
        /// This executes a SQL command that drops the course that the student is currently enrolled in.
        /// </summary>
        /// <param name="stuid">ID# of student that is dropping the course</param>
        /// <param name="courseid">ID of course the student is attempting to drop</param>
        /// <param name="termid">ID of the current term the student is enrolled in</param>
        /// Postcondition: The course has been dropped. 
        public void DropCourse(int stuid, string courseid, int termid)
        {
            SqlCommand drop_course = new SqlCommand("UPDATE TERM_ITEM SET DROPPED = 1 WHERE " +
            "STU_ID = " + stuid + " and COURSE_ID = '" + courseid + "' and TERM_ID = " + termid, db_connection);


            drop_course.ExecuteNonQuery();
        }

        /// <summary>
        /// Checks to see if a student has already taken and passed a given course.
        /// </summary>
        /// <param name="stuid">ID# of the student we are checking</param>
        /// <param name="courseid">ID of the course we are checking</param>
        /// <returns>Returns true if student has previously taken and passed a course, otherwise returns false</returns>
        public bool HasPassedCourse(int stuid, string courseid)
        {
            SqlCommand checkreg = new SqlCommand("SELECT * FROM TERM_ITEM WHERE STU_ID = " + stuid + " and " +
                "COURSE_ID = '" + courseid + "' and FINAL_GRADE >= 60", db_connection);

            SqlDataReader reader = checkreg.ExecuteReader();
            bool a = reader.Read();
            reader.Close();
            return a;
        }

        /// <summary>
        /// Checks to see is a student is currently taking a course in this term.
        /// </summary>
        /// <param name="stuid">ID number of student who we are checking.</param>
        /// <param name="courseid">Course ID for the course we are checking</param>
        /// <param name="termid">This is the term number for the current term of the current student.</param>
        /// <returns>Returns true if student is currently taking the course, else returns false.</returns>
        public bool IsTakingCourse(int stuid, string courseid, int termid)
        {
            SqlCommand checkreg = new SqlCommand("SELECT * FROM TERM_ITEM WHERE STU_ID = " + stuid + " and " +
                "COURSE_ID = '" + courseid + "' and TERM_ID = " + termid, db_connection);

            SqlDataReader reader = checkreg.ExecuteReader();
            bool a = reader.Read();
            reader.Close();
            return a;
        }

        /// <summary>
        /// Checks to see if a student has dropped a given course already during the current term
        /// </summary>
        /// <param name="stuid">ID# of the student we are checking</param>
        /// <param name="courseid">ID of the course we are checking</param>
        /// <param name="termid">ID of the current term</param>
        /// <returns>True if the course has been dropped during this term, otherwise false</returns>
        public bool HasDropped(int stuid, string courseid, int termid)
        {
            SqlCommand dropped = new SqlCommand("SELECT DROPPED FROM TERM_ITEM WHERE STU_ID = " + stuid +
                " and COURSE_ID = '" + courseid + "' and TERM_ID = " + termid, db_connection);
            SqlDataReader read = dropped.ExecuteReader();
            read.Read();

            if (read.HasRows == false)
            {
                read.Close();
                return false;
            }
            int result = Convert.ToInt32(read[0].ToString());
            read.Close();
            return Convert.ToBoolean(result);
        }

        public void UpdateFinalGrade(int itemid, int finalgrade)
        {
            SqlCommand dropped = new SqlCommand("UPDATE TERM_ITEM SET FINAL_GRADE = " + finalgrade +
                "WHERE ITEM_ID = " + itemid, db_connection);

            dropped.ExecuteNonQuery();
        }



        public DataTable GetCheckSheetMaybe(int stuid)
        {
            SqlCommand something = new SqlCommand("SELECT * FROM TERM_ITEM WHERE STU_ID = " + stuid, db_connection);
            SqlDataAdapter ad = new SqlDataAdapter(something);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            return dt;
        }


    }
}
